package com.hpe.util;

import com.hpe.pojo.SGrade;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;

/**
 * @author:CuiCaijin
 * @Description: 原网站excel操作
 * @Date:14:19 2017/10/10
 */
public class ExcelUtil {


    public static void main(String[] args) throws Exception {

        FileInputStream inputStream = new FileInputStream("C:\\Users\\chunjie\\Desktop\\3.xls");
        ExcelUtil excelUtil = new ExcelUtil(inputStream, "2007");
        String[] propertyNames = {"sId", "GradeId", "Value"};
        Class<?>[] propertyTypes = {Integer.class, Integer.class, Integer.class};
        List<SGrade> grades = excelUtil.readExcel(propertyNames, propertyTypes, SGrade.class);
        for (SGrade grade : grades) {
            System.out.println(grade);
        }

//
//        String path = "C:\\Users\\chunjie\\Desktop\\3.xls";
//        ExcelUtil excelUtil = new ExcelUtil("2007");
//        String[] beanProperty = {"Id", "sName", "sPwd"};
//        String[] colName = {"id", "姓名", "密码"};
//        Student[] students = {new Student(1, "2sss", "sss"), new Student(2, "ddds", "sd")};
//        ByteArrayOutputStream bos = excelUtil.getExcelStreamWithPointOutProperties(students, beanProperty, colName, "sheet1",path);
//        FileOutputStream fileOutputStream = new FileOutputStream(path);
//        bos.writeTo(fileOutputStream);
//        bos.close();
//        System.out.println("运行成功 ");
    }


    private Workbook workbook;

    private String version;

    public ExcelUtil(InputStream is, String version) throws IOException {
        if ("2003".equals(version)) {
            workbook = new HSSFWorkbook(is);
            this.version = "2003";
        } else {
            this.version = "2007";
            workbook = new XSSFWorkbook(is);
        }
    }

    public ExcelUtil(String version) throws IOException {
        if ("2003".equals(version)) {
            workbook = new HSSFWorkbook();
            this.version = "2003";
        } else {
            this.version = "2007";
            workbook = new XSSFWorkbook();
        }
    }

    /**
     * @Author:CuiCaijin
     * @Description: 读取excel中的内容
     * @Date:17:11 2017/10/10
     */
    public <T> List<T> readExcel(String[] beanProperties, Class<?>[] propertyTypes,
                                 Class<T> clazz) throws Exception {
        if (beanProperties == null || propertyTypes == null) {
            throw new Exception("必须指定Excel列映射的JavaBean属性及相应的数据类型");
        }
        if (beanProperties.length != propertyTypes.length) {
            throw new Exception("给定的JavaBean属性和数据类型不匹配");
        }
        Method[] setterMethods = new Method[beanProperties.length];
        int i = 0;
        for (String property : beanProperties) {
            //为每一个属性组装出setter方法名
            String setterMethodName = "set" + property;
            try {
                setterMethods[i] = clazz.getDeclaredMethod(setterMethodName, propertyTypes[i++]);
            } catch (SecurityException e) {
                throw new Exception("属性" + property + "对应的setter无法访问", e);
            } catch (NoSuchMethodException e) {
                try {
                    setterMethods[i - 1] = clazz.getSuperclass().getDeclaredMethod(setterMethodName, propertyTypes[i]);
                } catch (NoSuchMethodException ex) {
                    String paramType = propertyTypes[--i].getName();
                    throw new Exception("属性" + property + "没有参数类型为" + paramType + "的setter方法", ex);
                }
            }
        }
        return parseExcel(setterMethods, propertyTypes, clazz);
    }


    private <T> List<T> parseExcel(Method[] setterMethods, Class<?>[] propertyTypes,
                                   Class<T> beanType) throws Exception {
        Sheet sheet = workbook.getSheetAt(0);
        List<T> parseExcelResult = new ArrayList<T>((sheet.getLastRowNum() == 0) ? 0 : sheet.getLastRowNum() - 1);
        for (int row = 1, rows = sheet.getLastRowNum(); row <= rows; row++) {
            T instance = beanType.newInstance();
            Row currentRow = sheet.getRow(row);
            if (currentRow == null || isRowEmpty(currentRow)) {
                continue;
            }
            for (int current = 0, cellCount = currentRow.getLastCellNum(); current <= cellCount; current++) {
                if (current >= setterMethods.length) {
                    break;
                }
                Method setterMethod = setterMethods[current];
                Class<?> type = propertyTypes[current];
                if (null != setterMethod) {
                    invokeSetterMethod(setterMethod, instance, currentRow.getCell(current), type);
                }
            }
            parseExcelResult.add(instance);
        }
        workbook.close();
        return parseExcelResult;
    }

    /**
     * 出Excel单元格中的数据并作相应转换, 然后调用实例的设置属性值方法(setter), 给实例设置指定值
     *
     * @param setterMethod
     * @param o
     * @param cell
     * @param paramType
     * @throws Exception
     */
    @SuppressWarnings("deprecation")
    private void invokeSetterMethod(Method setterMethod, Object o, Cell cell,
                                    Class<?> paramType) throws Exception {
        try {
            if (String.class.isAssignableFrom(paramType) || char.class.isAssignableFrom(paramType)
                    || Character.class.isAssignableFrom(paramType)) {
                setterMethod.invoke(o, cell.getStringCellValue());
            } else if (Double.class.isAssignableFrom(paramType) || double.class.isAssignableFrom(paramType)) {
                setterMethod.invoke(o, cell.getNumericCellValue());
            } else if (Integer.class.isAssignableFrom(paramType) || int.class.isAssignableFrom(paramType)) {
                Double number = cell.getNumericCellValue();
                setterMethod.invoke(o, number.intValue());
            } else if (Long.class.isAssignableFrom(paramType) || long.class.isAssignableFrom(paramType)) {
                Double number = cell.getNumericCellValue();
                setterMethod.invoke(o, number.longValue());
            } else if (Float.class.isAssignableFrom(paramType) || float.class.isAssignableFrom(paramType)) {
                setterMethod.invoke(o, (float) cell.getNumericCellValue());
            } else if (Short.class.isAssignableFrom(paramType) || short.class.isAssignableFrom(paramType)) {
                Double number = cell.getNumericCellValue();
                setterMethod.invoke(o, number.shortValue());
            } else if (Boolean.class.isAssignableFrom(paramType) || boolean.class.isAssignableFrom(paramType)) {
                setterMethod.invoke(o, cell.getBooleanCellValue());
            } else if (Date.class.isAssignableFrom(paramType)) {
                setterMethod.invoke(o, cell.getDateCellValue());
            } else if (null == cell || "".equals(cell.getStringCellValue()) || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                setterMethod.invoke(o, new Object[]{null});
            } else {
                throw new Exception("方法" + setterMethod.getName() +
                        "所需要参数类型不被支持");
            }
        } catch (IllegalArgumentException e) {
            throw new Exception("setter方法" + setterMethod.getName() + "需要参数的类型为" +
                    setterMethod.getParameterTypes()[0].getName() +
                    ", 传入的类型为" + paramType.getName(), e);
        }
    }


    /**
     * <p>将Java对象组成的集合转换成Excel数据, 以输出流形式返回, 可以实现下载、存储在磁盘等.</p>
     * <p>Excel文件列按JavaBean属性定义顺序生成, 还需要注意Excel列名数与排除掉的属性数之和必须与
     * JavaBean属性总数相等.</p>
     *
     * @param dataList          需要转换到Excel的Java对象数组
     * @param columnNames       Excel列名(即JavaBean属性对应的列名, 如:name >> '姓名'), 必须按JavaBean
     *                          属性定义顺序给出, 还需要注意跳过excludeProperties指定的排除掉的属性
     * @param excludeProperties 排除掉的JavaBean属性名
     * @param sheetName         Excel文件表名
     * @return 生成的Excel文件输出流(ByteArrayOutputStream)
     * @throws Exception
     */
    public <T> OutputStream getExcelStream(T[] dataList, String[] columnNames,
                                           String[] excludeProperties, String sheetName, String path) throws Exception {
        if (null == columnNames) {
            throw new Exception("Excel列名必须指定.");
        }
        // 取得数组成员的类型Class对象
        Class<?> clazz = dataList.getClass().getComponentType();
        Field[] fields = clazz.getDeclaredFields();

        List<String> excludePropertyList = Collections.emptyList();
        if (excludeProperties != null && excludeProperties.length > 0) {
            excludePropertyList = Arrays.asList(excludeProperties);
        }

        if (fields.length != columnNames.length + excludePropertyList.size()) {
            throw new Exception("给定Excel列名为" + columnNames.length + "个, 排除掉的" +
                    "属性为" + excludePropertyList.size() + "个, 实体类" +
                    clazz.getSimpleName() + "共有" + fields.length +
                    "个属性,个数不匹配.");
        }
        // 列名与Bean有效属性一一对应
        String[] beanProperties = new String[columnNames.length];
        int i = 0;
        for (Field field : fields) {
            String fieldName = field.getName();
            if (excludePropertyList == null || !excludePropertyList.contains(fieldName)) {
                // 当给定排除属性数和列名数之和与Bean属性数不等时会有异常
                beanProperties[i++] = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
            }
        }
        Method[] getterMethods = parseGetterMethods(beanProperties, clazz);
        return getExcelStream(dataList, getterMethods, columnNames, sheetName, path);
    }

    /**
     * <p>将Java对象组成的集合转换成Excel数据, 以输出流形式返回, 可以实现下载、存储在磁盘等.</p>
     * <p>可按任意顺序取任意个数的Bean属性导出到Excel, Excel列顺序将与给定beanProperties顺序保持一致.</p>
     *
     * @param data           需要生成Excel文件的Java数据, beanProperties即是T中的属性
     * @param beanProperties 需要转换到Excel中的Bean属性
     * @param colNames       Excel使用的列名, 需要与beanProperties一一对应
     * @param sheetName      Excel表名
     * @return 生成的Excel输出流(ByteArrayOutputStream)
     * @throws Exception
     */
    public <T> ByteArrayOutputStream getExcelStreamWithPointOutProperties(T[] data, String[] beanProperties, String[] colNames,
                                                                          String sheetName, String path) throws Exception {
        if (beanProperties == null || colNames == null) {
            throw new Exception("必须给出有效的JavaBean属性及相应的Excel列名.");
        }
        if (beanProperties.length > colNames.length) {
            throw new Exception("给出的Excel列名为" + colNames.length +
                    "个, 给出的有效Bean属性为" + beanProperties.length + "个, 个数不匹配.");
        }
        Class<?> clazz = data.getClass().getComponentType();
        Method[] getterMethods = parseGetterMethods(beanProperties, clazz);

        return getExcelStream(data, getterMethods, colNames, sheetName, path);
    }

    /**
     * 原有基础加锁定列
     * @param data
     * @param beanProperties
     * @param colNames
     * @param sheetName
     * @param path
     * @param lock 锁定列
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> ByteArrayOutputStream getExcelStreamWithPointOutProperties(T[] data, String[] beanProperties, String[] colNames,
                                                                          String sheetName, String path, int[] lock) throws Exception {
        if (beanProperties == null || colNames == null) {
            throw new Exception("必须给出有效的JavaBean属性及相应的Excel列名.");
        }
        if (beanProperties.length > colNames.length) {
            throw new Exception("给出的Excel列名为" + colNames.length +
                    "个, 给出的有效Bean属性为" + beanProperties.length + "个, 个数不匹配.");
        }
        Class<?> clazz = data.getClass().getComponentType();
        Method[] getterMethods = parseGetterMethods(beanProperties, clazz);

        return getExcelStream(data, getterMethods, colNames, sheetName, path, lock);
    }

    private Method[] parseGetterMethods(String[] properties, Class<?> clazz)
            throws Exception {
        Method[] getterMethods = new Method[properties.length];
        int i = 0;
        for (String property : properties) {
            String getterName = "get" + property;
            try {
                getterMethods[i++] = clazz.getMethod(getterName);
            } catch (SecurityException e) {
                throw new Exception("属性" + property + "对应的getter方法可能无法访问.", e);
            } catch (NoSuchMethodException se) {
                throw new Exception("属性" + property + "没有对应的getter方法." + getterName, se);
            }
        }
        return getterMethods;
    }

    private <T> ByteArrayOutputStream getExcelStream(T[] data, Method[] getterMethods, String[] colNames,
                                                     String sheetName, String path) throws Exception {
        if (null == sheetName) {
            sheetName = "sheet1";
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        Sheet sheet = workbook.createSheet(sheetName);

        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setFontName("楷体");
        font.setFontHeightInPoints((short) 13);
        style.setFont(font);

        int col = 0;
        for (Method method : getterMethods) {
            int row = 0;
            Row currentRow = sheet.getRow(row);
            if (currentRow == null) {
                currentRow = sheet.createRow(row);
            }

            Cell cell = currentRow.createCell(col);
            cell.setCellStyle(style);
            cell.setCellValue(colNames[col]);

            for (T t : data) {
                invokeGetterMethod(method, t, sheet, style , ++row, col);
            }
            col++;
        }

        for (int i = 0; i < colNames.length; i++) {
            sheet.autoSizeColumn(i);
        }

        workbook.write(os);
        workbook.close();
        os.flush();
        os.close();
        return os;
    }

    /**
     * 原有基础加锁定列
     * @param data
     * @param getterMethods
     * @param colNames
     * @param sheetName
     * @param path
     * @param <T>
     * @return
     * @throws Exception
     */
    private <T> ByteArrayOutputStream getExcelStream(T[] data, Method[] getterMethods, String[] colNames,
                                                     String sheetName, String path, int[] lock) throws Exception {
        if (null == sheetName) {
            sheetName = "sheet1";
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        Sheet sheet = workbook.createSheet(sheetName);


        Font font = workbook.createFont();
        font.setFontName("楷体");
        font.setFontHeightInPoints((short) 13);

        CellStyle unlockStyle = workbook.createCellStyle();
        unlockStyle.setAlignment(HorizontalAlignment.CENTER);
        unlockStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        unlockStyle.setFont(font);
        unlockStyle.setLocked(false);

        CellStyle lockStyle = workbook.createCellStyle();
        lockStyle.setAlignment(HorizontalAlignment.CENTER);
        lockStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        lockStyle.setFont(font);
        lockStyle.setLocked(true);


        int col = 0;
        for (Method method : getterMethods) {
            int row = 0;
            Row currentRow = sheet.getRow(row);
            if (currentRow == null) {
                currentRow = sheet.createRow(row);
            }

            Cell cell = currentRow.createCell(col);
            cell.setCellStyle(lockStyle);
            cell.setCellValue(colNames[col]);

            for (T t : data) {
                invokeGetterMethod(method, t, sheet, unlockStyle, lockStyle, ++row, col, lock);
            }
            col++;
        }

        for (int i = 0; i < colNames.length; i++) {
            sheet.setColumnWidth(i, colNames[i].getBytes().length * 3 * 256);
        }

        //sheet锁定
        sheet.protectSheet("hpejnsd");

        workbook.write(os);
        workbook.close();
        os.flush();
        os.close();
        return os;
    }


    /**
     * 调用JavaBean中的取值方法, 并将返回值按照JavaBean属性的数据类型填充到Excel指定的单元格.
     *
     * @param getterMethod
     * @param o
     * @param sheet
     * @param rowIndex
     * @param colIndex
     * @throws Exception
     */
    private void invokeGetterMethod(Method getterMethod, Object o, Sheet sheet, CellStyle style,
                                    int rowIndex, int colIndex) throws Exception {
        Class<?> type = getterMethod.getReturnType();
        Object returnVal = getterMethod.invoke(o, (Object[]) null);
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        Cell cell = row.createCell(colIndex);
        cell.setCellStyle(style);
        if (null == returnVal) {
            cell.setCellValue("");
        } else if (String.class.isAssignableFrom(type) || char.class.isAssignableFrom(type) ||
                Character.class.isAssignableFrom(type)) {
            cell.setCellValue(returnVal.toString());
        } else if (Number.class.isAssignableFrom(type) || double.class.isAssignableFrom(type)
                || int.class.isAssignableFrom(type) || long.class.isAssignableFrom(type)
                || short.class.isAssignableFrom(type) || float.class.isAssignableFrom(type)) {
            cell.setCellValue(((Number) returnVal).doubleValue());
        } else if (Date.class.isAssignableFrom(type)) {
            cell.setCellValue((Date) returnVal);
        } else if (Boolean.class.isAssignableFrom(type) || boolean.class.isAssignableFrom(type)) {
            cell.setCellValue((Boolean) returnVal);
        } else {    // 不支持其它的返回值类型
            throw new Exception("getter方法: " + getterMethod.getName() +
                    "的返回值类型不被支持.");
        }
    }

    /**
     * 原有基础加锁定列
     * @param getterMethod
     * @param o
     * @param sheet
     * @param unlockStyle
     * @param lockStyle
     * @param rowIndex
     * @param colIndex
     * @param lock
     * @throws Exception
     */
    private void invokeGetterMethod(Method getterMethod, Object o, Sheet sheet, CellStyle unlockStyle,
                                    CellStyle lockStyle, int rowIndex, int colIndex, int[] lock) throws Exception {
        Class<?> type = getterMethod.getReturnType();
        Object returnVal = getterMethod.invoke(o, (Object[]) null);
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        Cell cell = row.createCell(colIndex);
        if (lock[colIndex] == 1) {
            cell.setCellStyle(lockStyle);
        }else {
            cell.setCellStyle(unlockStyle);
        }

        if (null == returnVal) {
            cell.setCellValue("");
        } else if (String.class.isAssignableFrom(type) || char.class.isAssignableFrom(type) ||
                Character.class.isAssignableFrom(type)) {
            cell.setCellValue(returnVal.toString());
        } else if (Number.class.isAssignableFrom(type) || double.class.isAssignableFrom(type)
                || int.class.isAssignableFrom(type) || long.class.isAssignableFrom(type)
                || short.class.isAssignableFrom(type) || float.class.isAssignableFrom(type)) {
            cell.setCellValue(((Number) returnVal).doubleValue());
        } else if (Date.class.isAssignableFrom(type)) {
            cell.setCellValue((Date) returnVal);
        } else if (Boolean.class.isAssignableFrom(type) || boolean.class.isAssignableFrom(type)) {
            cell.setCellValue((Boolean) returnVal);
        } else {    // 不支持其它的返回值类型
            throw new Exception("getter方法: " + getterMethod.getName() +
                    "的返回值类型不被支持.");
        }
    }


    @SuppressWarnings("deprecation")
    private boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }
}
